<?php defined('SYSPATH') or die('No direct script access.');
/**
 *
 * 采购付款单批量上传
 * @author locove
 *
 */
class Controller_Leadtime extends Controller
{
    /**
     * 描述 : 交期导入
     * 作者 : Edgar.lee
     */
    public function action_index() {
        if( PHP_SAPI !== 'cli' ) return;

        set_time_limit(0);                                                                                              //防超时
        ignore_user_abort(true);

        $maxDate = date('Y-m-d', $_SERVER['REQUEST_TIME']);
        $nowDate = date('Y-m-d H:i:s', $_SERVER['REQUEST_TIME']);
        echo "交期导入开始 : {$nowDate} \n";

        $sql = 'SELECT
            DATE_FORMAT(`add_time`, "%Y-%m-%d") maxDate
        FROM
            `ldt_base`
        ORDER BY
            `id` DESC 
        LIMIT 1';
        $minDate = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();                                    //读取数据库最大时间
        $minDate = empty($minDate) ? '1970-01-01' : $minDate[0]['maxDate'];                                             //生成最小范围的过滤条件
        $count = 0;                                                                                                     //计数;

        $sql = "SELECT
            UPPER(`warehouseorders`.`sku`) `sku`,                                                       /*大写 SKU*/
            `warehouseorders`.`supplier_id`,                                                            /*供应商ID*/
            `warehouseorders`.`purchaseorders_id`,                                                      /*采购单号*/
            UNIX_TIMESTAMP(`warehouseorders`.`warehouse_date`) `warehouse_date`,                        /*入库时间戳*/
            UNIX_TIMESTAMP(`purchaseorder_details`.details_date) details_date                           /*下单时间*/
        FROM
            `warehouseorders`
                LEFT JOIN `purchaseorder_details` ON
                    `purchaseorder_details`.purchaseorder_id = `warehouseorders`.`purchaseorders_id`    /*采购单主键*/
                AND `purchaseorder_details`.`sku` = `warehouseorders`.`sku`                             /*SKU主键*/
        WHERE
            `warehouseorders`.`purchaseorders_id` <> '0'
        AND `warehouseorders`.`type` = '10'
        AND `warehouseorders`.`warehouse_date` < '{$maxDate}'
         AND `warehouseorders`.`warehouse_date` >= '{$minDate}'";

        while( tool::eachSql($sql, $data) ) {
            foreach($data as &$v) {
                $temp = $v['warehouse_date'] - $v['details_date'];                                                      //时间戳差值
                $iSql = "INSERT INTO `ldt_base` (
                    `sku`, `su_id`, `pu_id`, `add_time`,
                    `put_sec`, `wat_sec`, `ldt_sec`
                ) SELECT 
                    '{$v['sku']}', '{$v['supplier_id']}', '{$v['purchaseorders_id']}', '{$nowDate}',
                    '{$v['details_date']}', '{$v['warehouse_date']}', '{$temp}'
                FROM 
                    (SELECT TRUE) `data` 
                WHERE 
                    NOT EXISTS(
                        SELECT 
                            TRUE 
                        FROM 
                            `ldt_base` 
                        WHERE 
                            `sku` = '{$v['sku']}' 
                        AND `pu_id` = '{$v['purchaseorders_id']}'
                        LIMIT 1
                    )";

                DB::query(Database::INSERT, $iSql)->execute('newerp');
                $count += 1;                                                                                            //导入计数
            }
        }

        $nowDate = time() - $_SERVER['REQUEST_TIME'];
        echo "交期导入结束 : 导入 {$count} 条, 耗时 {$nowDate} 秒\n";

        $this->countLdt($minDate);                                                                                      //交期统计开始
        $this->goMaxMinLeadTime($minDate);
    }

    /**
     * 描述 : 统计有效交期
     * 作者 : Edgar.lee
     */
    private function countLdt($minDate) {
        $nowDate = time();
        $ldtCoeff = Config::load('leadtime')->ldtCoeff;                                                                 //交期统计参数
        $ldtCount = Config::load('leadtime')->ldtCount;                                                                 //交期统计参数
        $count = 0;                                                                                                     //变更数量

        $sql = "SELECT
            `sku`
        FROM
            `ldt_base`
        WHERE
            `add_time` > '{$minDate}'
        GROUP BY
            `sku`";
        $data = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array(NULL,'sku');
        $skus_status = skusystemapi::getSkuAndStatus($data);
        $sku_sts = array();
        foreach($skus_status as $st) {
            $sku_sts[$st['sku']] = $st['status_id'];
        }

        $sql = "SELECT
            `sku`,`su_id`
        FROM
            `ldt_base`
        WHERE
            `add_time` > '{$minDate}'
        GROUP BY
            `sku`,`su_id`";                                                                                             //需统计的sku和供应商

        while( tool::eachSql($sql, $data) ) {
            foreach($data as &$vd) {
                $cSql = "SELECT
                    `ldt_sec`
                FROM
                    `ldt_base`
                WHERE
                    `sku` = '{$vd['sku']}'
                AND `su_id` = '{$vd['su_id']}'
                ORDER BY
                    /*`add_time` DESC,*/ `put_sec` DESC
                LIMIT 
                    {$ldtCount}";
                $cData = DB::query(Database::SELECT, $cSql)->execute('newerp')->as_array();                             //读取数据库最大时间
                foreach($cData as &$v) $v = $v['ldt_sec'];

                rsort($cData);
                if( isset($cData[$ldtCount - 1]) ) {                                                                    //长久供应商
                    $cData = $cData[1];
                } else if( isset($cData[2]) ) {                                                                         //大于等于3次的供应商
                    $cData = $cData[1];
                    /*$temp = $cData[count($cData) - 1] * $ldtCoeff;
                    foreach($cData as &$vc) {
                        if( $vc < $temp ) {                                                                             //找到小于最小交期的最大交期
                            $cData = $vc;
                            break;
                        }
                    }*/
                } else {                                                                                                //小于两次供应商取最大值
                    $cData = $cData[0];
                }

                $st = isset($sku_sts[$vd['sku']])?$sku_sts[$vd['sku']]:1;
                $iSql = "INSERT INTO `ldt_ord` (
                    `id`, `sku`, `su_id`, `ldt_sec`, `status`,
                    `add_time`, `up_time`
                ) SELECT
                    `data`.id, '{$vd['sku']}', '{$vd['su_id']}', '{$cData}',{$st},
                    NOW(), NOW()
                FROM
                    (SELECT TRUE) `temp` 
                        LEFT JOIN (SELECT 
                                id 
                            FROM
                                `ldt_ord`
                            WHERE
                                `sku` = '{$vd['sku']}'
                            AND `su_id` = '{$vd['su_id']}'
                        ) `data` ON
                            TRUE
                ON DUPLICATE KEY UPDATE 
                    `ldt_sec` = '{$cData}',
                    `up_time` = NOW()";

                DB::query(Database::INSERT, $iSql)->execute('newerp');
                $count += 1;                                                                                            //导入计数
            }
        }
        $this->update_ldt_ord("ldt_ord");
        $nowDate = time() - $nowDate;
        echo "交期统计结束 : 导入 {$count} 条, 耗时 {$nowDate} 秒\n";
    }
    //临时加的一个状态修改功能
    public function update_ldt_ord($tbl)
    {
        $skus = DB::select('sku')->from($tbl)->group_by('sku')->execute('newerp')->as_array(NULL,'sku');
        $have = DB::select('sku','status')->from($tbl)->execute('newerp')->as_array('sku','status');
        $skus_status = skusystemapi::getSkuAndStatus($skus);
        $sts = array();
        foreach($skus_status as $st) {
            $sts[$st['sku']] = $st['status_id'];
        }
        foreach($skus as $sku)
        {
            $s = isset($sts[$sku])?$sts[$sku]:1;
            if($s==$have[$sku])continue;
            DB::update($tbl)->set(array('status'=>$s))->where('sku','=',$sku)->execute('newerp');
        }
    }

    public function goMaxMinLeadTime($minDate) {
        $nowDate = time();
        $ldtCoeff = Config::load('leadtime')->ldtCoeff;                                                                 //交期统计参数
        $ldtCount = Config::load('leadtime')->ldtCount;                                                                 //交期统计参数
        $count = 0;                                                                                                     //变更数量

        $sql = "SELECT
            `sku`
        FROM
            `ldt_base`
        WHERE
            `add_time` > '{$minDate}'
        GROUP BY
            `sku`";
        $data = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array(NULL,'sku');
        $skus_status = skusystemapi::getSkuAndStatus($data);
        $sku_sts = array();
        foreach($skus_status as $st) {
            $sku_sts[$st['sku']] = $st['status_id'];
        }

        $sql = "SELECT
            `sku`,`su_id`
        FROM
            `ldt_base`
        WHERE
            `add_time` > '{$minDate}'
        GROUP BY
            `sku`,`su_id`";                                                                                             //需统计的sku和供应商

        $temps = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
        $skus = $su_ids = array();
        foreach($temps as $t) {
            $skus[] = $t['sku'];
            $su_ids[] = $t['su_id'];
        }

        while( tool::eachSql($sql, $data) ) {
            foreach($data as &$vd) {
                $cSql = "SELECT
                    `ldt_sec`
                FROM
                    `ldt_base`
                WHERE
                    `sku` = '{$vd['sku']}'
                AND `su_id` = '{$vd['su_id']}'
                ORDER BY
                    /*`add_time` DESC,*/ `put_sec` DESC
                LIMIT
                    {$ldtCount}";
                $cData = DB::query(Database::SELECT, $cSql)->execute('newerp')->as_array();                             //读取数据库最大时间
                foreach($cData as &$v) $v = $v['ldt_sec'];

                rsort($cData);
                $num = count($cData);
                if($num>3) {
                    unset($cData[$num-1],$cData[0]);
                    $ee1 = 0;
                    foreach($cData as $f) {
                        $ee1 += $f;
                    }
                    $cData = round($ee1/($num-2));
                }else{
                    $ee1 = 0;
                    foreach($cData as $f) {
                        $ee1 += $f;
                    }
                    $cData = round($ee1/$num);
                }

                $st = isset($sku_sts[$vd['sku']])?$sku_sts[$vd['sku']]:1;
                $iSql = "INSERT INTO `ldt_ords` (
                    `id`, `sku`, `su_id`, `ldt_sec`, `status`,
                    `add_time`, `up_time`
                ) SELECT
                    `data`.id, '{$vd['sku']}', '{$vd['su_id']}', '{$cData}',{$st},
                    NOW(), NOW()
                FROM
                    (SELECT TRUE) `temp`
                        LEFT JOIN (SELECT
                                id
                            FROM
                                `ldt_ords`
                            WHERE
                                `sku` = '{$vd['sku']}'
                            AND `su_id` = '{$vd['su_id']}'
                        ) `data` ON
                            TRUE
                ON DUPLICATE KEY UPDATE
                    `ldt_sec` = '{$cData}',
                    `up_time` = NOW()";

                DB::query(Database::INSERT, $iSql)->execute('newerp');
                $count += 1;                                                                                            //导入计数
            }
        }
        $this->update_ldt_ord("ldt_ords");
        $nowDate = time() - $nowDate;
        echo "交期去最大最小值求平均统计结束 : 导入 {$count} 条, 耗时 {$nowDate} 秒\n";
    }
}
